| 12345678910111213141516171819202122232425262728293031323334353637383940414243444546 |
- IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'View_tb_ErpProduct')
- BEGIN
- DROP VIEW [dbo].View_tb_ErpProduct
- END
- GO
- create View View_tb_ErpProduct
- as
- select tb_ErpProduct.ID,Prod_AlarmQuantity,
- Prod_Number,Prod_DividedShop,
- Prod_Name,Prod_Class,Prod_Unit,
- Prod_CostPrice,
- Prod_SalesPrice,
- Prod_SpecialPrice,
- Prod_PurchasePrice,
- Prod_IsRetail,
- Prod_IsEnabled,
- Prod_Introduction,
- Prod_Detail,
- Prod_Remark,
- Prod_Quantity,
- Prod_Thumbnail,
- Prod_FullSizeImage,
- Prod_Expedited,
- Prod_CreateDatetime,
- Prod_UpdateDatetime,
- Prod_Batch,
- Prod_ClassName,
- Sc_ClassCode,
- Sc_ClassName,
- Sc_ClassParentID,
- Sc_ClassRemark,
- Sc_IsDisable,
- Sc_IsReadOnly,
- Sc_URL,
- Sc_IsEnabledURL,
- Sc_CreateDatetime,
- Sc_UpdateDatetime,
- Sc_IsDelete ,
- Prod_IsEnabledName=CASE tb_ErpProduct.Prod_IsEnabled WHEN 1 THEN '上架' ELSE '下架' END ,
- dbo.fn_GetClassCodeToName(Prod_Class,Prod_ClassName) as Prod_ClassName2,
- (select Count(OPlist_ProdQuantity) from tb_ErpOrderProductList where OPlist_ProdNumber =Prod_Number and OPlist_Type = '2' and ((CONVERT(int,OPlist_CompletedStatus)+CONVERT(int,OPlist_PickupStatus))=0)) as Prod_PreSaleQuantity
- ,Prod_Availability
- from tb_ErpProduct left join tb_ErpSystemCategory on tb_ErpProduct.Prod_Class=tb_ErpSystemCategory.Sc_ClassCode
- GO
|
